package com.terrygjt.myshop;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.os.Handler;
import android.os.Message;
import android.util.Log;

import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Created by terry on 2019-05-18.
 */

public class Commodity_Sqlite extends SQLiteOpenHelper {
    public String tableName="Commodity";
    SQLiteDatabase mSQLiteDatabase;
    public Commodity_Sqlite(Context context) {
        super(context, "Commodity.db", null, 1);
        String path = Environment.getExternalStorageDirectory().getAbsolutePath()+"/terry";
        Log.i("线程queryAllCommodity","测试"+path);
        File pathFile = new File(path);
        File file = new File(path+"/Commodity.db");
        try{
            if(!pathFile.exists()){
                pathFile.mkdirs();
            }
            if(!file.exists()){
                file.createNewFile();
                mSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(file,null);
                mSQLiteDatabase.execSQL("create table "+tableName+" (commodityBarcode text PRIMARY KEY,commodityName text,commodityPrice1 text,commoditySpecification1 text," +
                        "commodityPrice2 text,commoditySpecification2 text,commodityPrice3 text,commoditySpecification3 text,updateTime text)");
            }
            mSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(file,null);
        } catch (IOException e) {
            Log.i("线程queryAllCommodity","测试"+e.getMessage());
//            e.printStackTrace();
        }
//        mSQLiteDatabase= this.getReadableDatabase();
    }
    public void deleteData(String commodityBarcode) {
        mSQLiteDatabase.delete(tableName,"commodityBarcode=?",new String[]{commodityBarcode});
    }
    public void updateData(String commodityBarcode, String commodityName, String commodityPrice1, String commoditySpecification1,
                           String commodityPrice2, String commoditySpecification2,String commodityPrice3, String commoditySpecification3) {
        ContentValues values=new ContentValues();
        values.put("commodityBarcode",commodityBarcode);
        values.put("commodityName",commodityName);
        values.put("commodityPrice1",commodityPrice1);
        values.put("commoditySpecification1",commoditySpecification1);
        values.put("commodityPrice2",commodityPrice2);
        values.put("commoditySpecification2",commoditySpecification2);
        values.put("commodityPrice3",commodityPrice3);
        values.put("commoditySpecification3",commoditySpecification3);
        values.put("updateTime",getStringDate());//修改当前时间
        mSQLiteDatabase.update(tableName,values,"commodityBarcode=?",new String[]{commodityBarcode});
    }
    
    public void insertData(String commodityBarcode, String commodityName, String commodityPrice1, String commoditySpecification1,
                           String commodityPrice2, String commoditySpecification2,String commodityPrice3, String commoditySpecification3) {
        ContentValues values=new ContentValues();
        values.put("commodityBarcode",commodityBarcode);
        values.put("commodityName",commodityName);
        values.put("commodityPrice1",commodityPrice1);
        values.put("commoditySpecification1",commoditySpecification1);
        values.put("commodityPrice2",commodityPrice2);
        values.put("commoditySpecification2",commoditySpecification2);
        values.put("commodityPrice3",commodityPrice3);
        values.put("commoditySpecification3",commoditySpecification3);
        values.put("updateTime",getStringDate());//插入当前时间
        mSQLiteDatabase.insert(tableName,null,values);
    }
    public void insertData(String commodityBarcode, String commodityName, String commodityPrice1, String commoditySpecification1,
                           String commodityPrice2, String commoditySpecification2) {
        insertData(commodityBarcode, commodityName, commodityPrice1, commoditySpecification1,
                commodityPrice2, commoditySpecification2,null,null);
    }
    public void insertData(String commodityBarcode, String commodityName, String commodityPrice1, String commoditySpecification1) {
        insertData(commodityBarcode, commodityName, commodityPrice1, commoditySpecification1,
                null, null,null,null);
    }
    /*
    *queryColumnNames表示要查询的列
    * 可以为：commodityBarcode, commodityName, commodityPrice1, commoditySpecification1
    * */
    public String[] queryData(String commodityBarcode, String[] queryColumnNames) {
        if(queryColumnNames==null){
            queryColumnNames=new String[]{"commodityBarcode","commodityName","commodityPrice1","commoditySpecification1",
                    "commodityPrice2", "commoditySpecification2","commodityPrice3", "commoditySpecification3"};
        }
        String[] result=new String[queryColumnNames.length];
        try {
//            各个参数的意思：
//            table:表名，不能为null
//            columns:要查询的列名，可以是多个，可以为null，表示查询所有列
//            selection:查询条件，可以为null,比如id=? and name=?
//            selectionArgs:对查询条件赋值，一个问号对应一个值，按顺序 可以为null
//            having:语法have，可以为null
//            orderBy：语法，按xx排序，可以为null
            Cursor cs = mSQLiteDatabase.query(tableName, queryColumnNames, "commodityBarcode=?", new String[]{commodityBarcode},
                    null, null, null);
            if(cs.moveToFirst()) {
                for (int i=0;i<queryColumnNames.length;i++){
                    result [i]=cs.getString(cs.getColumnIndex(queryColumnNames[i]));
                }
            }
            cs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    public void queryAllDataAndLog(Handler dataHandler) {
        String[] result=new String[8];
        try {
//            各个参数的意思：
//            table:表名，不能为null
//            columns:要查询的列名，可以是多个，可以为null，表示查询所有列
//            selection:查询条件，可以为null,比如id=? and name=?
//            selectionArgs:对查询条件赋值，一个问号对应一个值，按顺序 可以为null
//            having:语法have，可以为null
//            orderBy：语法，按xx排序，可以为null
            Cursor cs = mSQLiteDatabase.query(tableName, null, null, null,
                    null, null, null);
            if(cs.moveToFirst()) {
                do {
                    result[0]=cs.getString(cs.getColumnIndex("commodityBarcode"));
                    result[1]=cs.getString(cs.getColumnIndex("commodityName"));
                    result[2]=cs.getString(cs.getColumnIndex("commodityPrice1"));
                    result[3]=cs.getString(cs.getColumnIndex("commoditySpecification1"));
                    result[4]=cs.getString(cs.getColumnIndex("commodityPrice2"));
                    result[5]=cs.getString(cs.getColumnIndex("commoditySpecification2"));
                    result[6]=cs.getString(cs.getColumnIndex("commodityPrice3"));
                    result[7]=cs.getString(cs.getColumnIndex("commoditySpecification3"));

                    Log.i("线程queryAllDataAndLog",result [0]+"-"+result [1]+"-"+result [2]+"/"+result [3]+
                            "-"+result [4]+"/"+result [5]+"-"+result [6]+"/"+result [7]);
                    Message message=new Message();
                    message.what=6;
                    message.obj=result [0]+"-"+result [1]+"-"+result [2]+"/"+result [3];
                    dataHandler.sendMessage(message);
                }
                while (cs.moveToNext()) ;
            }
            cs.close();
        } catch (Exception e) {
            Log.i("线程queryAllCommodity","测试异常"+e.getMessage());
        }
    }
    private String getzhou(){
        String z="";
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try
        {
            Date d1 = new Date(System.currentTimeMillis());
            Date d2 = df.parse("2019-06-23 0:00:00");
            Date d3 = df.parse("2019-12-22 0:00:00");
            long time1 = d1.getTime() - d2.getTime();//这样得到的差值是微秒级别
            long day1 = time1 / (1000 * 60 * 60 * 24);
            long zhou=day1/7+1;
            long time2 = d3.getTime() - d1.getTime();//这样得到的差值是微秒级别
            long day2 = time2 / (1000 * 60 * 60 * 24)+1;
            //    long hours = (time1-day1*(1000 * 60 * 60 * 24))/(1000* 60 * 60);
            //    long minutes = (time1-day1*(1000 * 60 * 60 * 24)-hours*(1000* 60 * 60))/(1000* 60);
            Log.i("日期","这学期过了"+day1+"天今天是第"+zhou+"周");
            z="今天是在家复习第"+day1+"天\n\n考研还有："+day2+"天\n\nfighting！！！\n";
            Log.i("日期","z:"+z);
        }
        catch (Exception e)
        {
            Log.i("日期","报错"+e.getMessage());
        }
        return z;
    }
    private static String getStringDate() {
        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateString = formatter.format(currentTime);
        Log.i("线程getStringDate","更新时间:"+dateString);
        return dateString;
    }
    public void closeSQLiteDatabase(){
        if (mSQLiteDatabase!=null){
            mSQLiteDatabase.close();
        }
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
//        db.execSQL("create table "+tableName+" (commodityBarcode text PRIMARY KEY,commodityName text,commodityPrice1 text,commoditySpecification1 text," +
//                "commodityPrice2 text,commoditySpecification2 text,commodityPrice3 text,commoditySpecification3 text)");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}